﻿using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;

namespace OrionApplication.OrionLib.Dao
{
    public class DaoOrionUser : DaoBase
    {
        public DaoOrionUser()
            : base()
        {
            this.TableName = "OrionUser";
            this.ViewName = "VOrionUser";
        }

        public OrionUser SelectById(Int32 OrionId)
        {
            lParam.Clear();
            OrionUser retour = new OrionUser();
            String req = "SELECT OrionId, Nom, Prenom, Login, AES_DECRYPT(Password,'Orion') as Password, OrionMenuId, OrionGroupId, OrionCrea, OrionMaj, OrionEtat  FROM " 
                + this.TableName 
                + " WHERE OrionId=@orionid";
            
            MySqlParameter param = new MySqlParameter();
            param.ParameterName = "@orionid";
            param.Value = OrionId;
            
            lParam.Add(param);
            MySqlDataReader dr = this.ExecuteQueryReader(req, lParam);

            if (dr.Read())
                retour = ChargeFromReader(dr);
            dr.Close();
            return retour;
        }

        public OrionUser SelectByLogin(String Login)
        {
            lParam.Clear();
            OrionUser retour = new OrionUser();
            String req = "SELECT OrionId, Nom, Prenom, Login, AES_DECRYPT(Password,'Orion') as Password, OrionMenuId, OrionGroupId, OrionCrea, OrionMaj, OrionEtat  FROM "
                + this.TableName
                + " WHERE Login=@login";

            MySqlParameter param = new MySqlParameter();
            param.ParameterName = "@login";
            param.Value = Login;

            lParam.Add(param);
            MySqlDataReader dr = this.ExecuteQueryReader(req, lParam);

            if (dr.Read())
                retour = ChargeFromReader(dr);
            dr.Close();
            return retour;
        }

        public OrionUser Insert(OrionUser ou)
        {
            lParam.Clear();
            ou.OrionCrea = DateTime.Now;
            ou.OrionMaj = DateTime.Now;

            OrionUser retour = new OrionUser();
            String req = "INSERT INTO " + TableName + "(OrionId, Nom, Prenom, Login, Password, OrionMenuId, OrionGroupId, OrionCrea, OrionMaj, OrionEtat)  VALUES(NULL," +
                " @Nom, @Prenom, @Login, AES_ENCRYPT(@Password, 'Orion'), @OrionMenuId, @OrionGroupId, @OrionCrea, @OrionMaj, @OrionEtat)";

            MySqlParameter param = new MySqlParameter();
            param.ParameterName = "@Nom";
            param.Value = ou.Nom;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Prenom";
            param.Value = ou.Prenom;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Login";
            param.Value = ou.Login;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Password";
            param.Value = ou.Password;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionMenuId";
            param.Value = ou.OrionMenuId;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionGroupId";
            param.Value = ou.OrionGroupId;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionCrea";
            param.Value = ou.OrionCrea;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionMaj";
            param.Value = ou.OrionMaj;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionEtat";
            param.Value = ou.OrionEtat;
            lParam.Add(param);

            this.ExecuteNonQuery(req, lParam);
            retour = this.SelectById(Convert.ToInt32(this.LastInsertId));
            
            return retour;
        }

        public OrionUser Update(OrionUser ou)
        {
            lParam.Clear();
            ou.OrionMaj = DateTime.Now;

            OrionUser retour = new OrionUser();
            String req = "UPDATE " + TableName + " SET Nom=@Nom, Prenom=@Prenom, Login=@Login, Password=AES_ENCRYPT(@Password, 'Orion'), OrionMenuId=@OrionMenuId, OrionGroupId=@OrionGroupId, OrionMaj=@OrionMaj, OrionEtat=@OrionEtat " +
                " WHERE OrionId=@OrionId";

            MySqlParameter param = new MySqlParameter();
            param.ParameterName = "@Nom";
            param.Value = ou.Nom;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionId";
            param.Value = ou.OrionId;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Prenom";
            param.Value = ou.Prenom;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Login";
            param.Value = ou.Login;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Password";
            param.Value = ou.Password;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionMenuId";
            param.Value = ou.OrionMenuId;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionGroupId";
            param.Value = ou.OrionGroupId;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionMaj";
            param.Value = ou.OrionMaj;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionEtat";
            param.Value = ou.OrionEtat;
            lParam.Add(param);

            this.ExecuteNonQuery(req, lParam);
            retour = this.SelectById(ou.OrionId);

            return retour;
        }

        private OrionUser ChargeFromReader(MySqlDataReader dr)
        {
            OrionUser retour = new OrionUser();
            retour.OrionId = dr.GetInt32("OrionId");

            if (dr.IsDBNull(1))
                retour.Nom = string.Empty;
            else
                retour.Nom = dr.GetString("Nom");
            if (dr.IsDBNull(2))
                retour.Prenom = string.Empty;
            else
                retour.Prenom = dr.GetString("Prenom");
            if (dr.IsDBNull(3))
                retour.Login = string.Empty;
            else
                retour.Login = dr.GetString("Login");
            if (dr.IsDBNull(4))
                retour.Password = string.Empty;
            else
                retour.Password = dr.GetString("Password");
            if (dr.IsDBNull(5))
                retour.OrionMenuId = 0;
            else
                retour.OrionMenuId = dr.GetInt32("OrionMenuId");
            if (dr.IsDBNull(6))
                retour.OrionGroupId = 0;
            else
                retour.OrionGroupId = dr.GetInt32("OrionGroupId");

            retour.OrionCrea = dr.GetDateTime("OrionCrea");
            retour.OrionMaj = dr.GetDateTime("OrionMaj");
            retour.OrionEtat = dr.GetBoolean("OrionEtat");

            return retour;
        }

        public List<OrionUser> SelectAll()
        {
            lParam.Clear();
            List<OrionUser> retour = new List<OrionUser>();
            String req = this.DebutSelect
                + this.TableName;

            MySqlDataReader dr = this.ExecuteQueryReader(req);

            while (dr.Read())
                retour.Add(ChargeFromReader(dr));
            dr.Close();
            return retour;
        }
    }
}
